﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.DomainObject.Administration.UserManagement;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;

using System.Data.OleDb;
using System.IO;

namespace SIPO.Library.DataAccess.Transaction
{
    public partial class recLoadReconRepo
    {

        public bool Save(recLoadRecon master, List<recLoadReconDetail> details)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO transactions.recLoadRecon(BankID, BankName, LoadDate, AttachFileName, Attachment, Note, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate) VALUES(@BankID, @BankName, @LoadDate, @AttachFileName, @Attachment, @Note, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate)";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@BankID", SqlDbType.VarChar).Value = master.BankID;
                    cmd.Parameters.Add("@BankName", SqlDbType.VarChar).Value = master.BankName;
                    cmd.Parameters.Add("@LoadDate", SqlDbType.Date).Value = (object)master.LoadDate ?? DBNull.Value;
                    cmd.Parameters.Add("@AttachFileName", SqlDbType.VarChar).Value = (object)master.AttachFileName ?? DBNull.Value;
                    cmd.Parameters.Add("@Attachment", SqlDbType.VarBinary).Value = (object)master.Attachment ?? DBNull.Value;
                    cmd.Parameters.Add("@Note", SqlDbType.VarChar).Value = (object)master.Note ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)master.CreatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)master.CreatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)master.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)master.UpdatedDate ?? DBNull.Value;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // Save Master
                    cmd.Dispose();

                    cmd = conn.CreateCommand();
                    cmd.CommandText = " SELECT MAX(LoadReconID) LoadReconID FROM transactions.recLoadRecon WHERE CreatedBy = @CreatedBy ";
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = master.CreatedBy;

                    cmd.Transaction = txn;
                    var objLoadReconID = cmd.ExecuteScalar(); // Get LoadReconID
                    cmd.Dispose();

                    int loadReconID = objLoadReconID == DBNull.Value ? 0 : Convert.ToInt32(objLoadReconID.ToString());
                    master.LoadReconID = loadReconID;

                    details.ForEach(delegate(recLoadReconDetail domain)
                    {
                        cmd = conn.CreateCommand();
                        cmd.CommandText = "INSERT INTO transactions.recLoadReconDetail(LoadReconID, TransDate, TransID, Bravia, InvoiceNo, TransNote, MutationType, MutationValue, Balance, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate) VALUES(@LoadReconID, @TransDate, @TransID, @Bravia, @InvoiceNo, @TransNote, @MutationType, @MutationValue, @Balance, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate)";
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.Add("@LoadReconID", SqlDbType.Int).Value = master.LoadReconID;
                        cmd.Parameters.Add("@TransDate", SqlDbType.Date).Value = domain.TransDate;
                        cmd.Parameters.Add("@TransID", SqlDbType.VarChar).Value = (object)domain.TransID ?? DBNull.Value;
                        cmd.Parameters.Add("@Bravia", SqlDbType.VarChar).Value = (object)domain.Bravia ?? DBNull.Value;
                        cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)domain.InvoiceNo ?? DBNull.Value;
                        cmd.Parameters.Add("@TransNote", SqlDbType.VarChar).Value = (object)domain.TransNote ?? DBNull.Value;
                        cmd.Parameters.Add("@MutationType", SqlDbType.VarChar).Value = (object)domain.MutationType ?? DBNull.Value;
                        cmd.Parameters.Add("@MutationValue", SqlDbType.Decimal).Value = (object)domain.MutationValue ?? DBNull.Value;
                        cmd.Parameters.Add("@Balance", SqlDbType.Decimal).Value = (object)domain.Balance ?? DBNull.Value;
                        cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domain.CreatedBy ?? DBNull.Value;
                        cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)domain.CreatedDate ?? DBNull.Value;
                        cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                        cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;

                        cmd.Transaction = txn;
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    });

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }


            }
            return result;
        }
        public List<LoadDataReconsiliasiDTO> GetData(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
    {
        recPaymentRepo repo = new recPaymentRepo(connectionString);
            DataTable dt = new DataTable();
     List<LoadDataReconsiliasiDTO> LoadDataReconsiliasiDTOs = new List<LoadDataReconsiliasiDTO>();
             using (SqlConnection conn = new SqlConnection(connectionString))
             {
                 using (SqlCommand cmd = conn.CreateCommand())
                 {
                     int startRow = ((currentPage - 1) * pageSize) + 1;
                     int endRow = (startRow + pageSize);

                     StringBuilder sbQuery = new StringBuilder();
                     sbQuery.AppendFormat(" WITH result_set AS ");
                     sbQuery.AppendFormat(" ( ");
                     sbQuery.AppendFormat("    SELECT ");
                     sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);       
             sbQuery.AppendFormat(@"isNull(bb.invoiceNo,IsNull(aa.InvoiceNo,cc.transBankId)) IdTransaksi, 
			 isNull(cc.AdjustmentTypeName,dd.ForPayment) Keterangan, 
			 IsNull(bb.DebtorSectorID,0) DebtorSectorID, 
			 ee.debtorName,  
			 cc.AdjusValue, cc.TransId, aa.transNote keteranganBank, 
			 bb.invoiceNo PaymentinvoiceNo, bb.TransDate as payment, bb.paymentValue, 
			 aa.transId bankTransId, aa.InvoiceNo as Invoicebank,
             aa.mutationValue
             from transactions.recLoadReconDetail aa
             left join transactions.recPayment bb on bb.InvoiceNo = aa.InvoiceNo  ");
			 sbQuery.AppendFormat(" and {0}", parameters);
             sbQuery.AppendFormat(@" left join transactions.recAdjus cc on transBankId = aa.transId
             left join transactions.recBilling dd on dd.InvoiceNo = bb.InvoiceNo 
             left join v_map_debtorsector ee on ee.DebtorSectorID =  bb.DebtorSectorID ");
             sbQuery.AppendFormat("where {0}", parameters.Replace("bb","aa") );
                       sbQuery.AppendFormat(" ) ");
                     sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                     cmd.CommandText = sbQuery.ToString();
                     cmd.CommandType = CommandType.Text;
                       try
                     {
                         conn.Open();
                         using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                         {
                           
                             while (dr.Read())
                             {
                                 LoadDataReconsiliasiDTO recon = new LoadDataReconsiliasiDTO();
                                 recon.Pungutan_IDTransaksi = dr[8] == DBNull.Value ? string.Empty: dr[1].ToString();
                                 recon.Pungutan_Keterangan = dr[2] == DBNull.Value ? string.Empty : dr[2].ToString();
                                 recon.Pungutan_Jumlah = dr[10] == DBNull.Value ? (decimal?)null : (decimal?)dr[10];
                                 recon.Pungutan_Debitur = dr[4] == DBNull.Value ? string.Empty : dr[4].ToString();
                                 recon.Data_Bank_Keterangan = dr[7] == DBNull.Value ? string.Empty : dr[7].ToString();
                                 recon.Data_Bank_IDTransaksi = dr[12] == DBNull.Value ? string.Empty : dr[12].ToString();
                                 recon.Data_Bank_Jumlah = dr[13] == DBNull.Value ? (decimal?)null : (decimal?)dr[13];
                                 recon.Status = repo.CheckInvoiceRecon(recon.Data_Bank_IDTransaksi);
                                 recon.TransID = dr[11] == DBNull.Value ? string.Empty : dr[11].ToString();
                                 if (recon.Status.ToLower() == "penyesuaian")
                                 {
                                     if (recon.Pungutan_IDTransaksi != string.Empty)
                                         recon.Status = "";

                                 }
                                 //    recLoadReconDetail recLoadReconDetail = new recLoadReconDetail();
                                 //    recLoadReconDetailRepo recLoadReconDetailRepo = new recLoadReconDetailRepo(connectionString);
                                 //    recLoadReconDetail = recLoadReconDetailRepo.GetByInvoiceNumber(recon.Data_Bank_IDTransaksi);


                                 //    if (repo.CheckDataOnAdjustmentByTransID(recLoadReconDetail.TransID) == false)
                                 //    {
                                 //        LoadDataReconsiliasiDTOs.Add(recon);
                                 //    }
                                 //    else
                                 //        LoadDataReconsiliasiDTOs.Add(recon);
                                 //}
                                 //else
                                 //{
                                 //    LoadDataReconsiliasiDTOs.Add(recon);
                                 //}
                                  LoadDataReconsiliasiDTOs.Add(recon);
                           
                             }
                         }
                        // LoadDataReconsiliasiDTOs = dt;

                     }
                       catch (Exception ex)
                       {
                           throw (ex);
                       }
                       finally
                       {
                           if (conn.State == ConnectionState.Open) conn.Close();
                       }
                 }
             }
        return LoadDataReconsiliasiDTOs;
    }
        public object SelectScalarReport(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) ", column);
                            sbQuery.AppendFormat(@"  from transactions.recLoadReconDetail aa
                            left join transactions.recPayment bb on bb.InvoiceNo = aa.InvoiceNo  ");
			                sbQuery.AppendFormat(" and {0}", parameters);
                            sbQuery.AppendFormat(@" left join transactions.recAdjus cc on transBankId = aa.transId
                            left join transactions.recBilling dd on dd.InvoiceNo = bb.InvoiceNo 
                            left join v_map_debtorsector ee on ee.DebtorSectorID =  bb.DebtorSectorID ");
                            sbQuery.AppendFormat("where {0}", parameters.Replace("bb","aa") );
                            break;
                        case SQL.Function.Aggregate.Min:
                           sbQuery.AppendFormat("SELECT MIN({0}) ", column);
                            sbQuery.AppendFormat(@"  from transactions.recLoadReconDetail aa
                            left join transactions.recPayment bb on bb.InvoiceNo = aa.InvoiceNo  ");
			                sbQuery.AppendFormat(" and {0}", parameters);
                            sbQuery.AppendFormat(@" left join transactions.recAdjus cc on transBankId = aa.transId
                            left join transactions.recBilling dd on dd.InvoiceNo = bb.InvoiceNo 
                            left join v_map_debtorsector ee on ee.DebtorSectorID =  bb.DebtorSectorID ");
                            sbQuery.AppendFormat("where {0}", parameters.Replace("bb","aa") );
                            break;
                        case SQL.Function.Aggregate.Distinct:
                           sbQuery.AppendFormat("SELECT DISTINCT({0}) ", column);
                            sbQuery.AppendFormat(@"  from transactions.recLoadReconDetail aa
                            left join transactions.recPayment bb on bb.InvoiceNo = aa.InvoiceNo  ");
			                sbQuery.AppendFormat(" and {0}", parameters);
                            sbQuery.AppendFormat(@" left join transactions.recAdjus cc on transBankId = aa.transId
                            left join transactions.recBilling dd on dd.InvoiceNo = bb.InvoiceNo 
                            left join v_map_debtorsector ee on ee.DebtorSectorID =  bb.DebtorSectorID ");
                            sbQuery.AppendFormat("where {0}", parameters.Replace("bb","aa") );
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) ", column);
                            sbQuery.AppendFormat(@"  from transactions.recLoadReconDetail aa
                            left join transactions.recPayment bb on bb.InvoiceNo = aa.InvoiceNo  ");
			                sbQuery.AppendFormat(" and {0}", parameters);
                            sbQuery.AppendFormat(@" left join transactions.recAdjus cc on transBankId = aa.transId
                            left join transactions.recBilling dd on dd.InvoiceNo = bb.InvoiceNo 
                            left join v_map_debtorsector ee on ee.DebtorSectorID =  bb.DebtorSectorID ");
                            sbQuery.AppendFormat("where {0}", parameters.Replace("bb","aa") );
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) ", column);
                            sbQuery.AppendFormat(@"  from transactions.recLoadReconDetail aa
                            left join transactions.recPayment bb on bb.InvoiceNo = aa.InvoiceNo  ");
			                sbQuery.AppendFormat(" and {0}", parameters);
                            sbQuery.AppendFormat(@" left join transactions.recAdjus cc on transBankId = aa.transId
                            left join transactions.recBilling dd on dd.InvoiceNo = bb.InvoiceNo 
                            left join v_map_debtorsector ee on ee.DebtorSectorID =  bb.DebtorSectorID ");
                            sbQuery.AppendFormat("where {0}", parameters.Replace("bb","aa") );
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) ", column);
                            sbQuery.AppendFormat(@"  from transactions.recLoadReconDetail aa
                            left join transactions.recPayment bb on bb.InvoiceNo = aa.InvoiceNo  ");
			                sbQuery.AppendFormat(" and {0}", parameters);
                            sbQuery.AppendFormat(@" left join transactions.recAdjus cc on transBankId = aa.transId
                            left join transactions.recBilling dd on dd.InvoiceNo = bb.InvoiceNo 
                            left join v_map_debtorsector ee on ee.DebtorSectorID =  bb.DebtorSectorID ");
                            sbQuery.AppendFormat("where {0}", parameters.Replace("bb","aa") );
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }
    }
}
